﻿using NPOI.HSSF.Model;
using NPOI.HSSF.Record;
using NPOI.HSSF.Record.Aggregates;
using NPOI.OpenXmlFormats.Spreadsheet;
using NPOI.SS.Formula;
using NPOI.SS.Util;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Star.Tools.Excel;
using Star.Tools.Excel.Attributes;
using Star.Tools.Excel.Model;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using Star.Tools.Excel.Extensions;

namespace Star.Tools.Excel
{
    public static class NpoiHelper
    {
        #region 常量 

        public const string ExcelVersionXls = ".xls";

        public const string ExcelVersionXlsx = ".xlsx";
        #endregion 常量

        #region 属性
        private static IWorkbook _workbook;
        public static IWorkbook Workbook
        {
            get { return _workbook; }

            private set
            {
                if (value != _workbook)
                {

                }
                _workbook = value;
            }
        }

        private static ISheet _ISheet;
        #endregion 属性

        #region 公共
        /// <summary>
        /// 判断是否为兼容模式ture 2007 false  2003
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static bool GetIsCompatible(string filePath)
        {
            string ext = Path.GetExtension(filePath);
            return new[] { ".xlsx", ".xltx" }.Count(e => e.Equals(ext, StringComparison.OrdinalIgnoreCase)) > 0;
        }
        /// <summary>
        /// 写入流
        /// </summary>
        /// <returns></returns>
        public static MemoryStream WriteStream()
        {
            //写入内存流
            using (MemoryStream stream = new MemoryStream())
            {
                _workbook.Write(stream);
                return stream;
            }
        }

        /// <summary>
        /// 文件流转Iworkbook
        /// </summary>
        /// <param name="stream"></param>
        public static IWorkbook StreamToWorkbook(Stream stream)
        {
            if (stream == null)
                throw new ArgumentNullException(nameof(stream));
            using (stream)
            {
                return WorkbookFactory.Create(stream);
            }
        }



        #endregion

        #region 导出Excel
        public static void ExportExcel<T>(this IEnumerable<T> list) 
        {
            list.ExportExcel(ExcelFormat.xlsx);
        }
        public static void ExportExcel<T>(this IEnumerable<T> list, ExcelFormat version) 
        {
            list.ExportExcel(version, "Sheet1");
        }
        public static void ExportExcel<T>(this IEnumerable<T> list, ExcelFormat version, string SheetName) 
        {
            InitializeWorkbook(version);
            ISheet sheet = _workbook.CreateSheet(SheetName);
            CreateTitle<T>(sheet);
            list.CreateRows(sheet);
        }


        public static void Export<T>(this IEnumerable<T> list) where T : class, new()
        {
          
        }


        /// <summary>
        /// 生成文件 
        /// </summary>
        /// <param name="workbook">workbook</param>
        /// <param name="fullFilePath">文件路径</param>
        public static void WriteToFile(string fullFilePath)
        {
            WriteToFile(_workbook, fullFilePath);
        }
        /// <summary>
        /// 生成文件 
        /// </summary>
        /// <param name="workbook">workbook</param>
        /// <param name="fullFilePath">文件路径</param>
        public static void WriteToFile(this IWorkbook workbook, string fullFilePath)
        {
            if (workbook == null) { return; }
            if (!Directory.Exists(Path.GetDirectoryName(fullFilePath)))
            {
                Directory.CreateDirectory(Path.GetDirectoryName(fullFilePath));
            }
            using (FileStream fs = new FileStream(fullFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                fs.Position = 0;
                workbook.Write(fs);
                fs.Dispose();
            }
        }
        #endregion

        #region 构造创建工作簿

        #region 初始化工作簿
        public static IWorkbook GetWorkbook()
        {
            return _workbook;
        }

        /// <summary>
        /// 初始化工作簿 true  2007 flase 2003 
        /// </summary>
        /// <param name="isCompatible"></param>
        public static void InitializeWorkbook(bool isCompatible = true)
        {
            if (isCompatible)
            {
                _workbook = new XSSFWorkbook();
            }
            else
            {
                _workbook = new HSSFWorkbook();  //HSSFWorkbook只能操作excel2003一下版本
            }
        }
        /// <summary>
        /// 初始化工作簿
        /// </summary>
        /// <param name="ExcelVersion"></param>
        public static void InitializeWorkbook(ExcelFormat version)
        {
            switch (version)
            {
                case ExcelFormat.xlsx:
                    _workbook = new XSSFWorkbook();
                    break;
                case ExcelFormat.xls:
                    _workbook = new HSSFWorkbook();
                    break;
                default: throw new InvalidEnumArgumentException("version不存在", (int)version, version.GetType());
            }
        }

        #endregion 初始化工作簿


        #region 读取excel文件到IWorkbook



        /// <summary>
        /// 读取excel文件到IWorkbook
        /// </summary>
        /// <param name="fullFileName">excel文件全路径</param>
        /// <returns>IWorkbook对象</returns>
        private static IWorkbook ReadExcel(string fullFileName)
        {
            using (FileStream fs = new FileStream(fullFileName, FileMode.Open, FileAccess.Read))
            {
                if (fs == null) { return null; }
                if (fullFileName.ToLower().EndsWith(ExcelVersionXlsx))
                {
                    _workbook = new XSSFWorkbook(fs);
                }
                else
                {
                    _workbook = new HSSFWorkbook(fs);
                }
                fs.Dispose();
                return _workbook;
            }
        }

        public static IWorkbook ReadExcel(string path, bool relaxed = true)
        {
            if (string.IsNullOrEmpty(path))
                throw new ArgumentException(nameof(path));

            var share = relaxed ? FileShare.ReadWrite : FileShare.Read;
            using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, share))
            {
                return WorkbookFactory.Create(stream);
            }
        }
        /// <summary>
        /// 读取excel文件到IWorkbook
        /// </summary>
        /// <param name="excelFile"></param>
        /// <returns></returns>
        private static IWorkbook ExcelToWorkbook(string excelFile)
        {

            if (Path.GetExtension(excelFile).Equals(ExcelVersionXls))
            {
                using (var file = new FileStream(excelFile, FileMode.Open, FileAccess.Read))
                {
                    return new HSSFWorkbook(file);
                }
            }
            else if (Path.GetExtension(excelFile).Equals(ExcelVersionXlsx))
            {
                using (var file = new FileStream(excelFile, FileMode.Open, FileAccess.Read))
                {
                    return new XSSFWorkbook(file);
                }
            }
            else
            {
                throw new NotSupportedException($" {excelFile}不是Excel文件 ");
            }
        }


        /// <summary>
        /// 获得上传Excel文件
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public static IWorkbook GetWorkbook(HttpRequestBase request)
        {
            if (request.Files.Count == 0)
            {
                throw new Exception("没有上传文件");
            }
            var fileUpload = request.Files[0];
            string fileExtension = Path.GetExtension(fileUpload.FileName).ToLower();
            if (fileExtension.Equals(ExcelVersionXls))
            {
                InitializeWorkbook(ExcelFormat.xls);
            }
            else
            {
                InitializeWorkbook(ExcelFormat.xlsx);
            }
            return _workbook;
        }
        #endregion


        #endregion 构造创建工作簿

        #region 创建Sheet页

        /// <summary>
        /// 创建Sheet页
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        private static void CreateISheet(string sheetName)
        {
            if (_workbook != null)
            {
                _ISheet = _workbook.CreateSheet(sheetName);
            }
            else
            {
                InitializeWorkbook(ExcelFormat.xlsx);
                CreateISheet(sheetName);
            }

        }



        #endregion 创建Sheet页

        #region Excel标题

        private static void ExcelTitle(ISheet sheet, string title, int rowIndex = 0)
        {
            IRow rowTitle = sheet.CreateRow(rowIndex);
        }
        #endregion

        #region 创建标题行

        /// <summary>
        /// 创建标题行
        /// </summary>
        /// <param name="titles">标题数组</param>
        /// <param name="rowTitle">标题行</param>
        private static void CreateTitle(ISheet sheet, string[] titles, int rowIndex = 0)
        {
            IRow rowTitle = sheet.CreateRow(rowIndex);
            for (int i = 0; i < titles.Length; i++)
            {
                rowTitle.CreateCell(i).SetCellValue(titles[i]);
            }
        }

        /// <summary>
        /// 创建标题行
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheet"></param>
        /// <param name="rowIndex"></param>
        private static void CreateTitle<T>(ISheet sheet, int rowIndex = 0)
        {
            IRow rowTitle = sheet.CreateRow(rowIndex);
            PropertyInfo[] propertyInfos = typeof(T).GetProperties();

            ICellStyle cellStyle = CellStyleExtensions.Instance.GetTitleStyle(_workbook);

            int cellIndex = 0;
            foreach (var propInfo in propertyInfos)
            {
                var attributes = propInfo.GetCustomAttributes(typeof(ExcelColumnAttribute), true);
                if (attributes.Length < 1)
                { continue; }
                else
                {
                    ExcelColumnAttribute attr = attributes[0] as ExcelColumnAttribute;
                    if (!attr.IsIgnore)
                    {
                        ICell cell = rowTitle.CreateCell(cellIndex);
                        cell.SetColumnWidth(attr.ColumnWidth);
                        cell.CellStyle = cellStyle;
                        cell.SetCellValue(attr.ColumnName);
                    }
                    else
                    {
                        continue;
                    }
                    //var value = ((ExcelColumnAttribute)(attributes[0])).ColumnName;
                    //rowTitle.CreateCell(cellIndex).SetCellValue(value);
                }
                cellIndex++;
            }
        }


        #endregion

        #region 创建数据行
        private static void CreateRows<T>(this IEnumerable<T> list, ISheet sheet, int rowIndex = 1)
        {
            PropertyInfo[] properties = typeof(T).GetProperties();

            foreach (var item in list)
            {
                int _rowCell = 0;
                IRow rows = sheet.CreateRow(rowIndex);
                foreach (var property in properties)
                {
                    if (!property.GetIsIgnore() && property.GetCustomAttributes(typeof(ExcelColumnAttribute), true).Length > 0)
                    {
                        object value = property.GetValue(item);
                        ICell cell = rows.CreateCell(_rowCell);
                        cell.SetCellValues(value, property.GetExcelCellStyleEnum());
                        _rowCell++;
                    }
                }
                rowIndex++;
            }
        }

        #endregion

        #region 设置单元格值

        /// <summary>
        ///设置单元格值
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <param name="value">值</param>
        public static void SetCellValues(this ICell cell, object value, ExcelCellStyleEnum excelStyle)
        {
            TypeCode typeCode = Type.GetTypeCode(value.GetType());
            ICellStyle dateStyle = CellStyleExtensions.Instance.GetContentStyle(_workbook);
            dateStyle =CellStyleExtensions.GetCellStyle(dateStyle, excelStyle);
            //ICellStyle dateStyle = _workbook.CreateCellStyle();
            //IDataFormat format = _workbook.CreateDataFormat();
            //dateStyle.DataFormat = format.GetFormat("yyyy/MM/dd");
            switch (typeCode)
            {
                case TypeCode.String: //字符串类型
                    cell.SetCellValue(Convert.ToString(value));
                    cell.CellStyle = dateStyle;
                    break;

                case TypeCode.DateTime: //日期类型
                                        // ICellStyle dateStyle = _workbook.CreateCellStyle();
                    IDataFormat format = _workbook.CreateDataFormat();
                    dateStyle.DataFormat = format.GetFormat("yyyy/MM/dd");
                    cell.SetCellValue(Convert.ToDateTime(value));
                    cell.CellStyle = dateStyle;
                    break;

                case TypeCode.Boolean: //布尔型
                    cell.SetCellType(CellType.Boolean);
                    cell.SetCellValue(Convert.ToBoolean(value));
                    cell.CellStyle = dateStyle;
                    break;

                case TypeCode.Int16: //整型
                case TypeCode.Int32:
                case TypeCode.Int64:
                case TypeCode.UInt16: //无符号整型
                case TypeCode.UInt32:
                case TypeCode.UInt64:
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(Convert.ToDouble(value));
                    cell.CellStyle = dateStyle;
                    break;
                case TypeCode.Byte:
                case TypeCode.Single: //浮点型
                case TypeCode.Double:
                case TypeCode.Decimal:
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(Convert.ToDouble(value));
                    cell.CellStyle = dateStyle;
                    break;
                case TypeCode.DBNull:
                case TypeCode.Empty:
                    cell.SetCellType(CellType.Blank);
                    cell.SetCellValue(string.Empty);
                    cell.CellStyle = dateStyle;
                    break;
                default:
                    cell.SetCellValue(string.Empty);
                    cell.CellStyle = dateStyle;
                    break;
            }

        }

        #endregion

        #region  导出Excel可以多个Sheet不能合并单元格
        public static void ExportExcelToList(this List<ExcelExportEntity> source, ExcelFormat version)
        {
            if (source == null)
            {
                throw new ArgumentNullException(nameof(source));
            }
            if (_workbook == null)
            {
                InitializeWorkbook(version);

            }
            foreach (var excelExportEntity in source)
            {
                var typeName = excelExportEntity.Type;
                if (string.IsNullOrEmpty(excelExportEntity.SheetName))
                    excelExportEntity.SheetName = typeName.Name;
                CreateISheet(excelExportEntity.SheetName);
                List<PropertyInfo> propertyInfos = typeName.GetExcelColumns();
                CreateTitle(propertyInfos);
                CreateRows(excelExportEntity.ListSource, propertyInfos);

            }



        }

        /// <summary>
        /// 创建标题行
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheet"></param>
        /// <param name="rowIndex"></param>
        private static void CreateTitle(List<PropertyInfo> propertyInfos, int rowIndex = 0)
        {
            IRow rowTitle = _ISheet.CreateRow(rowIndex);
            ICellStyle cellStyle = CellStyleExtensions.Instance.GetTitleStyle(_workbook);
            int cellIndex = 0;
            foreach (var propInfo in propertyInfos)
            {
                var attributes = propInfo.GetCustomAttributes(typeof(ExcelColumnAttribute), true);
                if (attributes.Length < 1)
                { continue; }
                else
                {
                    ExcelColumnAttribute attr = attributes[0] as ExcelColumnAttribute;
                    if (!attr.IsIgnore)
                    {
                        ICell cell = rowTitle.CreateCell(cellIndex);
                        cell.SetColumnWidth(attr.ColumnWidth);
                        cell.CellStyle = cellStyle;
                        cell.SetCellValue(attr.ColumnName);
                    }
                    else
                    {
                        continue;
                    }
                }
                cellIndex++;
            }
        }
        private static void CreateRows<T>(this IEnumerable<T> list, List<PropertyInfo> propertyInfos, int rowIndex = 1)
        {
            ICellStyle dateStyle = CellStyleExtensions.Instance.GetContentStyle(_workbook);

            foreach (var item in list)
            {
                int _rowCell = 0;
                IRow rows = _ISheet.CreateRow(rowIndex);
                foreach (var property in propertyInfos)
                {
                    if (!property.GetIsIgnore() && property.GetCustomAttributes(typeof(ExcelColumnAttribute), true).Length > 0)
                    {
                        object value = property.GetValue(item);
                        ICell cell = rows.CreateCell(_rowCell);
                         cell.SetCellValues(value, property.GetExcelCellStyleEnum());
                        _rowCell++;
                    }
                }
                rowIndex++;
            }
        }

        #endregion


        public static List<TEntity> ToEntityList<TEntity>(this IWorkbook workbook, int sheetIndex)
         where TEntity : new()
        {
            if (workbook.NumberOfSheets <= sheetIndex)
            {
      
            }
            var sheet = workbook.GetSheetAt(sheetIndex);
            return SheetToEntityList<TEntity>(sheet);
        }

        public static List<TEntity> SheetToEntityList<TEntity>(ISheet sheet) {

           // if (sheet.FirstRowNum < 0)
                return new List<TEntity>();


        }

    }
}